const xlsx = require('xlsx');

/**
 * Excel 文件解析服务
 */
class ExcelService {
  /**
   * 解析 Excel 文件
   * @param {string} filePath - Excel 文件路径
   * @returns {Array} 解析后的数据数组
   */
  parseExcelFile(filePath) {
    try {
      // 读取 Excel 文件
      const workbook = xlsx.readFile(filePath);

      // 获取第一个工作表
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];

      // 先读取所有行（不使用第一行作为标题）
      const allRows = xlsx.utils.sheet_to_json(worksheet, {
        header: 1, // 使用数组格式，不自动识别标题
        defval: null,
        raw: false
      });

      // 查找真正的列标题行（包含"准考证号"或"姓名"的行）
      let headerRowIndex = -1;
      let headerRow = null;

      for (let i = 0; i < Math.min(10, allRows.length); i++) {
        const row = allRows[i];
        const rowStr = row.join('').toLowerCase();
        if (rowStr.includes('准考证') || rowStr.includes('学号') || (rowStr.includes('姓名') && rowStr.includes('班级'))) {
          headerRowIndex = i;
          headerRow = row;
          break;
        }
      }

      if (headerRowIndex === -1) {
        throw new Error('未找到列标题行，请确保 Excel 文件包含"准考证号"或"姓名"等列标题');
      }

      console.log('找到列标题行（第' + (headerRowIndex + 1) + '行）:', headerRow);

      // 使用找到的标题行解析数据
      const dataRows = allRows.slice(headerRowIndex + 1);
      const rawData = dataRows.map(row => {
        const obj = {};
        headerRow.forEach((header, index) => {
          if (header && header.trim()) {
            obj[header.trim()] = row[index];
          }
        });
        return obj;
      });

      // 解析数据
      const parsedData = this.parseRows(rawData);

      return parsedData;
    } catch (error) {
      throw new Error(`Excel 文件解析失败: ${error.message}`);
    }
  }

  /**
   * 解析数据行
   * @param {Array} rawData - 原始数据
   * @returns {Array} 解析后的数据
   */
  parseRows(rawData) {
    const results = [];

    rawData.forEach((row, index) => {
      // 跳过空行
      if (this.isEmptyRow(row)) {
        return;
      }

      try {
        const parsedRow = this.parseRow(row, index + 2); // +2 因为第1行是标题，Excel行号从1开始
        results.push(parsedRow);
      } catch (error) {
        results.push({
          row: index + 2,
          error: error.message,
          rawData: row
        });
      }
    });

    return results;
  }

  /**
   * 解析单行数据
   * @param {Object} row - 行数据
   * @param {number} rowNumber - 行号
   * @returns {Object} 解析后的行数据
   */
  parseRow(row, rowNumber) {
    // 列名映射（支持多种列名变体）
    const columnMap = {
      studentId: ['准考证号', '学号', '考号', '准考证'],
      className: ['班级', '所在班级'],
      name: ['姓名', '学生姓名'],
      chinese: ['语文'],
      math: ['数学'],
      english: ['英语'],
      physics: ['物理'],
      chemistry: ['化学'],
      biology: ['生物'],
      politics: ['政治'],
      history: ['历史'],
      geography: ['地理'],
      totalScore: ['总分', '总成绩'],
      schoolRank: ['校次', '排名', '校内排名']
    };

    const result = {
      row: rowNumber,
      studentId: this.getValueByColumnNames(row, columnMap.studentId),
      className: this.getValueByColumnNames(row, columnMap.className),
      name: this.getValueByColumnNames(row, columnMap.name),
      chinese: this.parseScore(this.getValueByColumnNames(row, columnMap.chinese)),
      math: this.parseScore(this.getValueByColumnNames(row, columnMap.math)),
      english: this.parseScore(this.getValueByColumnNames(row, columnMap.english)),
      physics: this.parseScore(this.getValueByColumnNames(row, columnMap.physics)),
      chemistry: this.parseScore(this.getValueByColumnNames(row, columnMap.chemistry)),
      biology: this.parseScore(this.getValueByColumnNames(row, columnMap.biology)),
      politics: this.parseScore(this.getValueByColumnNames(row, columnMap.politics)),
      history: this.parseScore(this.getValueByColumnNames(row, columnMap.history)),
      geography: this.parseScore(this.getValueByColumnNames(row, columnMap.geography)),
      totalScore: this.parseScore(this.getValueByColumnNames(row, columnMap.totalScore)),
      schoolRank: this.parseRank(this.getValueByColumnNames(row, columnMap.schoolRank))
    };

    return result;
  }

  /**
   * 根据多个可能的列名获取值
   * @param {Object} row - 行数据
   * @param {Array} columnNames - 可能的列名数组
   * @returns {*} 列值
   */
  getValueByColumnNames(row, columnNames) {
    // 首先尝试精确匹配
    for (const colName of columnNames) {
      if (row.hasOwnProperty(colName) && row[colName] !== null && row[colName] !== undefined && row[colName] !== '') {
        return row[colName];
      }
    }

    // 如果精确匹配失败，尝试模糊匹配（去除空格、忽略大小写）
    const rowKeys = Object.keys(row);
    for (const colName of columnNames) {
      const normalizedColName = colName.replace(/\s/g, '').toLowerCase();
      for (const key of rowKeys) {
        const normalizedKey = key.replace(/\s/g, '').toLowerCase();
        if (normalizedKey === normalizedColName || normalizedKey.includes(normalizedColName) || normalizedColName.includes(normalizedKey)) {
          if (row[key] !== null && row[key] !== undefined && row[key] !== '') {
            return row[key];
          }
        }
      }
    }

    return null;
  }

  /**
   * 解析分数
   * @param {*} value - 原始值
   * @returns {number|null} 分数
   */
  parseScore(value) {
    if (value === null || value === undefined || value === '') {
      return null;
    }

    const score = parseFloat(value);
    return isNaN(score) ? null : score;
  }

  /**
   * 解析排名
   * @param {*} value - 原始值
   * @returns {number|null} 排名
   */
  parseRank(value) {
    if (value === null || value === undefined || value === '') {
      return null;
    }

    const rank = parseInt(value);
    return isNaN(rank) ? null : rank;
  }

  /**
   * 判断是否为空行
   * @param {Object} row - 行数据
   * @returns {boolean} 是否为空行
   */
  isEmptyRow(row) {
    return Object.values(row).every(value =>
      value === null || value === undefined || value === ''
    );
  }
}

module.exports = new ExcelService();
